#! /bin/bash
if [[ $1 == "" ]];then
   TD_DATE=`date -d '1 days ago' "+%Y-%m-%d"`
else
   TD_DATE=$1
fi
echo '========================================'
echo '==============首次全量导入==============='
echo '========================================'
hive  -S -e  "
--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;

--============事实表==========--
-- ft_customer_clue客户线索表
insert overwrite table bxg_dwd.ft_customer_clue  partition (start_date)
select
id,
create_date_time,
update_date_time,
year(create_date_time) as year,
substr(create_date_time,1,7) as year_month,
month(create_date_time) as month,
substr(create_date_time,1,10) as year_month_day,
substr(create_date_time,1,19)  as day,
deleted,
customer_id,
customer_relationship_id,
session_id,
sid,
status,
\`user\`,
create_time,
platform,
s_name,
seo_source,
seo_keywords,
ip,
referrer,
from_url,
landing_page_url,
url_title,
to_peer,
manual_time,
begin_time,
reply_msg_count,
total_msg_count,
msg_count,
comment,
finish_reason,
finish_user,
end_time,
platform_description,
browser_name,
os_info,
area,
country,
province,
city,
creator,
name,
idcard,
phone,
itcast_school_id,
itcast_school,
itcast_subject_id,
itcast_subject,
wechat,
qq,
email,
gender,
level,
case when origin_type='NETSERVICE' or origin_type='PRESIGNUP' then 'ONLINE'
     else 'OFFLINE' end as origin_type,
information_way,
working_years,
technical_directions,
customer_state,
valid,
anticipat_signup_date,
clue_state,
scrm_department_id,
superior_url,
superior_source,
landing_url,
landing_source,
info_url,
info_source,
origin_channel,
course_id,
course_name,
zhuge_session_id,
is_repeat,
tenant,
activity_id,
activity_name,
follow_type,
shunt_mode_id,
shunt_employee_group_id,
'9999-99-99' as end_date,
'${TD_DATE}' as  start_date
from
bxg_ods.customer_clue where id is not null;

-- ft_customer_relationship客户意向表
insert overwrite table bxg_dwd.ft_customer_relationship partition (start_date)
select
id,
create_date_time,
update_date_time,
year(create_date_time) as year,
substr(create_date_time,1,7) as year_month,
month(create_date_time) as month,
substr(create_date_time,1,10) as year_month_day,
substr(create_date_time,1,19)  as day,
deleted,
customer_id,
first_id,
belonger,
belonger_name,
initial_belonger,
distribution_handler,
business_scrm_department_id,
last_visit_time,
next_visit_time,
case when origin_type='NETSERVICE' or origin_type='PRESIGNUP' then 'ONLINE'
     else 'OFFLINE' end as origin_type,
itcast_school_id,
itcast_subject_id,
intention_study_type,
anticipat_signup_date,
level,
creator,
current_creator,
creator_name,
origin_channel,
comment,
first_customer_clue_id,
last_customer_clue_id,
process_state,
process_time,
payment_state,
payment_time,
signup_state,
signup_time,
notice_state,
notice_time,
lock_state,
lock_time,
itcast_clazz_id,
itcast_clazz_time,
payment_url,
payment_url_time,
ems_student_id,
delete_reason,
deleter,
deleter_name,
delete_time,
course_id,
course_name,
delete_comment,
close_state,
close_time,
appeal_id,
tenant,
total_fee,
belonged,
belonged_time,
belonger_time,
transfer,
transfer_time,
follow_type,
transfer_bxg_oa_account,
transfer_bxg_belonger_name,
'9999-99-99' as end_date,
'${TD_DATE}' as start_date
from bxg_ods.customer_relationship where id is not null;

--============维度表==========--
-- dt_customer客户信息表
insert overwrite table bxg_dwd.dt_customer partition (start_date)
select
id,
customer_relationship_id,
create_date_time,
update_date_time,
year(create_date_time) as year,
substr(create_date_time,1,7) as year_month,
month(create_date_time) as month,
substr(create_date_time,1,10) as year_month_day,
substr(create_date_time,1,19)  as day,
deleted,
name,
idcard,
birth_year,
gender,
phone,
wechat,
qq,
email,
area,
leave_school_date,
graduation_date,
bxg_student_id,
creator,
origin_type,
origin_channel,
tenant,
md_id,
'9999-99-99' as end_date,
'${TD_DATE}' as start_date
from
bxg_ods.customer where id is not null;

-- dt_customer_appeal 线索申诉表
insert overwrite table bxg_dwd.dt_customer_appeal partition (start_date)
select
id,
customer_relationship_first_id,
employee_id,
employee_name,
employee_department_id,
employee_tdepart_id,
appeal_status,
audit_id,
audit_name,
audit_department_id,
audit_department_name,
audit_date_time,
create_date_time,
update_date_time,
year(create_date_time) as year,
substr(create_date_time,1,7) as year_month,
month(create_date_time) as month,
substr(create_date_time,1,10) as year_month_day,
substr(create_date_time,1,19)  as day,
deleted,
tenant,
'9999-99-99' as end_date,
'${TD_DATE}' as start_date
from
bxg_ods.customer_appeal where id is not null;

-- dt_employee员工表
insert overwrite table bxg_dwd.dt_employee partition (start_date)
select
id,
email,
real_name,
phone,
department_id,
department_name,
remote_login,
job_number,
cross_school,
last_login_date,
creator,
create_date_time,
update_date_time,
year(create_date_time) as year,
substr(create_date_time,1,7) as year_month,
month(create_date_time) as month,
substr(create_date_time,1,10) as year_month_day,
substr(create_date_time,1,19)  as day,
deleted,
scrm_department_id,
leave_office,
leave_office_time,
reinstated_time,
superior_leaders_id,
tdepart_id,
tenant,
ems_user_name,
'9999-99-99' as end_date,
'${TD_DATE}' as start_date
from
bxg_ods.employee where id is not null;

-- dt_itcast_clazz班级信息表
insert overwrite table bxg_dwd.dt_itcast_clazz partition (dt)
select
id,
create_date_time,
update_date_time,
year(create_date_time) as year,
substr(create_date_time,1,7) as year_month,
month(create_date_time) as month,
substr(create_date_time,1,10) as year_month_day,
substr(create_date_time,1,19)  as day,
deleted,
itcast_school_id,
itcast_school_name,
itcast_subject_id,
itcast_subject_name,
itcast_brand,
clazz_type_state,
clazz_type_name,
teaching_mode,
start_time,
end_time,
comment,
detail,
uncertain,
tenant,
'${TD_DATE}' as dt
from
bxg_ods.itcast_clazz where id is not null;

-- dt_itcast_school校区信息表
insert overwrite  table bxg_dwd.dt_itcast_school
select
id,
create_date_time,
update_date_time,
deleted,
name,
code,
tenant
from
bxg_ods.itcast_school where id is not null;

-- dt_itcast_subject学科信息表
insert overwrite table bxg_dwd.dt_itcast_subject
select
id,
create_date_time,
update_date_time,
year(create_date_time) as year,
substr(create_date_time,1,7) as year_month,
month(create_date_time) as month,
substr(create_date_time,1,10) as year_month_day,
substr(create_date_time,1,19)  as day,
deleted,
name,
code,
tenant
from
bxg_ods.itcast_subject where id is not null;

-- dt_scrm_department部门信息表
insert overwrite table bxg_dwd.dt_scrm_department partition (start_date)
select
id,
name,
parent_id,
create_date_time,
update_date_time,
year(create_date_time) as year,
substr(create_date_time,1,7) as year_month,
month(create_date_time) as month,
substr(create_date_time,1,10) as year_month_day,
substr(create_date_time,1,19)  as day,
deleted,
id_path,
tdepart_code,
creator,
depart_level,
depart_sign,
depart_line,
depart_sort,
disable_flag,
tenant,
'9999-99-99'as end_date,
'${TD_DATE}' as start_date
from
bxg_ods.scrm_department where id is not null;


"